package Information_sysyem_jdbc.Dao.impl;

import Information_sysyem_jdbc.Dao.InformationDao;
import Information_sysyem_jdbc.Utiles.JdbcUtils;
import Information_sysyem_jdbc.entity.Information;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

/**
 * @author LiangHaiBo
 * @version 1.0
 * @date 2021.8.21 上午 9:35
 */
public class InformationDaoimpl implements InformationDao {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    @Override
    public List<Information> selectFindAll() {
        try {
             conn = JdbcUtils.getConnection();
             String sql = "select * from Information ";
             stmt = conn.prepareStatement(sql);
             rs = stmt.executeQuery();
             List<Information> list = new ArrayList<>();
             Information information = null;
             while (rs.next()){
                 information =  new Information();
                 int id = rs.getInt("id");
                 String name = rs.getString("name");
                 String gender = rs.getString("gender");
                 int age = rs.getInt("age");
                 String address = rs.getString("address");
                 String phone = rs.getString("phone");

                 information.setId(id);
                 information.setName(name);
                 information.setGender(gender);
                 information.setAge(age);
                 information.setAddress(address);
                 information.setPhone(phone);

                 list.add(information);
             }
             return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.close(rs,stmt,conn);
        }
        return null;
    }

    @Override
    public Information selectfindByid(int id) {
        Information information = new Information();
        if(id<=0 || id>selectAllCount()){
            return null;
        }else {
            try {
                conn = JdbcUtils.getConnection();
                String sql = "select * from Information where id = ?";
                stmt = conn.prepareStatement(sql);
                stmt.setInt(1, id);
                rs = stmt.executeQuery();
                while (rs.next()) {
                    int id1 = rs.getInt("id");
                    String name = rs.getString("name");
                    String gender = rs.getString("gender");
                    int age = rs.getInt("age");
                    String address = rs.getString("address");
                    String phone = rs.getString("phone");

                    information.setId(id1);
                    information.setName(name);
                    information.setGender(gender);
                    information.setAge(age);
                    information.setAddress(address);
                    information.setPhone(phone);
                }
                return information;
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtils.close(rs, stmt, conn);
            }
            return information;
        }
    }

    @Override
    public int selectAllCount() {
        int account = 0;
        conn = JdbcUtils.getConnection();

        try {
            String sql = "Select id from Information";
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            while (rs.next()) {
                account++;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(rs, stmt, conn);
        }

        return account;
    }

    @Override
    public int add(Information information) {
        try {
            conn = JdbcUtils.getConnection();
            String sql = "insert into Information (id,name,gender,age,address,phone) values(?,?,?,?,?,?)";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1,information.getId());
            stmt.setString(2,information.getName());
            stmt.setString(3,information.getGender());
            stmt.setInt(4,information.getAge());
            stmt.setString(5,information.getAddress());
            stmt.setString(6,information.getPhone());
            int i = stmt.executeUpdate();
            System.out.println(i+"行受到影响");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.close(stmt,conn);
        }
        return 0;
    }

    @Override
    public int delete(int id) {
        try {
            conn = JdbcUtils.getConnection();
            String sql ="delete from Information where id = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1,id);
            int i = stmt.executeUpdate();
            System.out.println(i+"行受到影响");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.close(stmt,conn);
        }
        return 0;
    }

    @Override
    public int update(int id) {
        int count = 0;
        if(id<=0 || id>selectAllCount()){
           if(selectfindByid(id)==null){
               System.out.println("用户不存在");
           }
        }else {
            System.out.println(selectfindByid(id));
            conn = JdbcUtils.getConnection();
            Scanner sc  = new Scanner(System.in);
            System.out.println("1.姓名");
            System.out.println("2.性别");
            System.out.println("3,年龄");
            System.out.println("4.地址");
            System.out.println("5.手机号");
            System.out.println("6.退出");
            System.out.println("请选择要修改的内容序号:");
            String choice = sc.next();
            switch (choice){
                case "1":
                    String sql = "update Information set name =? where id =?";
                    try {
                        stmt = conn.prepareStatement(sql);
                        updatecount(id, sc);
                        count = stmt.executeUpdate();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }finally {
                        JdbcUtils.close(stmt,conn);
                    }
                    break;
                case "2":
                    sql = "update Information set gender =? where id =?";
                    try {
                        stmt = conn.prepareStatement(sql);
                        updatecount(id, sc);
                        count = stmt.executeUpdate();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }finally {
                        JdbcUtils.close(stmt,conn);
                    }
                    break;
                case"3":
                    sql = "update Information set age =? where id =?";
                try {
                    stmt = conn.prepareStatement(sql);
                    updatecount(id, sc);
                    count = stmt.executeUpdate();
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally {
                    JdbcUtils.close(stmt,conn);
                }
                break;
                case"4":
                    sql = "update Information set address =? where id =?";
                    try {
                        stmt = conn.prepareStatement(sql);
                        updatecount(id, sc);
                        count = stmt.executeUpdate();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }finally {
                        JdbcUtils.close(stmt,conn);
                    }
                    break;
                case"5":
                    sql = "update Information set phone =? where id =?";
                    try {
                        stmt = conn.prepareStatement(sql);
                        updatecount(id, sc);
                        count = stmt.executeUpdate();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }finally {
                        JdbcUtils.close(stmt,conn);
                    }
                    break;
                case "6":
                    break;
            }
        }
        System.out.println(selectfindByid(id));
        return count;
    }

    @Override
    public List<Information> limit(int page, int pagesize) {
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select * from Information limit ?,?";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1,(page-1)*pagesize);
            stmt.setInt(2,pagesize);
            rs = stmt.executeQuery();
            List<Information> list = new ArrayList<>();
            Information information = null;
            while (rs.next()){
                information =  new Information();
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
                int age = rs.getInt("age");
                String address = rs.getString("address");
                String phone = rs.getString("phone");

                information.setId(id);
                information.setName(name);
                information.setGender(gender);
                information.setAge(age);
                information.setAddress(address);
                information.setPhone(phone);

                list.add(information);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    private void updatecount(int id, Scanner sc) throws SQLException {
        System.out.println("请输入修改信息:");
        stmt.setString(1, sc.next());
        stmt.setInt(2, id);
    }
}
